package com.util;

import com.bean.Farmer;
import com.bean.ProductCategory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

public class SelectProductCategory {
    private String id = "sa";
    private String pwd = "123456";
    private String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=FarmProductSalesSystem";
    private List<ProductCategory> productCategoryList;
    public List<ProductCategory> getProductCategoryData(){
        productCategoryList = new ArrayList<>();
        try {
            //加载驱动
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //获取连接
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(url,id,pwd);
            String sql = "select * from ProductCategory";
            stat = conn.createStatement(); //用于执行sql语句
            rs = stat.executeQuery(sql); //返回查询后的结果
            while(rs.next()){
                String CategoryID = rs.getString("CategoryID");
                String CategoryName = rs.getString("CategoryName");
                String Remarks = rs.getString("Remarks");
                ProductCategory productCategory = new ProductCategory(Integer.parseInt(CategoryID),CategoryName,Remarks);
                productCategoryList.add(productCategory);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("连接数据库失败！");
        }finally{
            //关闭coon
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭stat
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭rs
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return productCategoryList;
    }

    public int getProductCategoryID(){
        List<ProductCategory> list = getProductCategoryData();
        int ID = 0;
        for(ProductCategory productCategory:list)
        {
            ID = productCategory.getCategoryID();
        }
        return ID;
    }
    public boolean notexitProductCategoryID(int ProductCategoryID){
        List<ProductCategory> list = getProductCategoryData();
        int ID = 0;
        for(ProductCategory productCategory:list)
        {
            ID = productCategory.getCategoryID();
            if(ID==ProductCategoryID)
                return false;
        }
        return true;
    }

    public int getProductCategoryID(String ProductCategoryName){
        List<ProductCategory> list = getProductCategoryData();
        int ID = 0;
        for(ProductCategory productCategory:list)
        {
            if(productCategory.getCategoryName().equals(ProductCategoryName))
                return productCategory.getCategoryID();
        }
        return 0;
    }
}
